package com.xbongbong.paas.util;


import com.xbongbong.paas.constant.ExcelConstant;
import lombok.Getter;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * POI读取excel有两种模式，一种是用户模式，一种是事件驱动模式
 * 采用SAX事件驱动模式解决XLSX文件，可以有效解决用户模式内存溢出的问题，
 * 该模式是POI官方推荐的读取大数据的模式，
 * 在用户模式下，数据量较大，Sheet较多，或者是有很多无用的空行的情况下，容易出现内存溢出
 * 用于解决.xlsx2007版本大数据量问题
 *
 * process()方式主要是遍历所有的sheet，并依次调用startElement()、characters()方法、endElement()这三个方法。startElement()用于设定单元格的数字类型（如日期、数字、字符串等等）。characters()用于获取该单元格对应的索引值或是内容值（如果单元格类型是字符串、INLINESTR、数字、日期则获取的是索引值；其他如布尔值、错误、公式则获取的是内容值）。endElement()根据startElement()的单元格数字类型和characters()的索引值或内容值，最终得出单元格的内容值，并打印出来。
 * @author 魏荣杰
 * @date 2019/11/11 2:15 下午
 * @since v4.6.0
 * @version v4.6.0
 */
public class ExcelXlsxHandler extends DefaultHandler {

    private ExcelReadDataDelegated excelReadDataDelegated;

    public ExcelReadDataDelegated getExcelReadDataDelegated() {
        return excelReadDataDelegated;
    }

    public void setExcelReadDataDelegated(ExcelReadDataDelegated excelReadDataDelegated) {
        this.excelReadDataDelegated = excelReadDataDelegated;
    }

    public ExcelXlsxHandler(ExcelReadDataDelegated excelReadDataDelegated) {
        this.excelReadDataDelegated = excelReadDataDelegated;
    }

    /**
     * 单元格中的数据可能的数据类型
     */
    @Getter
    enum CellDataType {
        /**
         * bool
         */
        BOOL("b"),
        /**
         * 错误
         */
        ERROR("e"),
        /**
         * 公式
         */
        FORMULA("str"),
        INLINESTR("inlineStr"),
        /**
         * 字符串
         */
        SSTINDEX("s"),
        /**
         * 数字
         */
        NUMBER(""),
        /**
         * 日期
         */
        DATE("m/d/yy"),
        /**
         * 空
         */
        NULL("");

        private String name;

        CellDataType(String name) {
            this.name = name;
        }

    }
    /**
     * 共享字符串表,对应sharedString.xml
     */
    private SharedStringsTable sharedStringsTable;
    /**
     * 上一次的索引值，上一次的内容
     */
    private String lastContent;
    /**
     * 文件的绝对路径
     */
//    private String filePath = "";
    /**
     * 工作表索引
     */
    private int sheetIndex = 0;

    /**
     * sheet名
     */
    private String sheetName = "";
    /**
     * 总行数
     */
    private int totalRows=0;

    /**
     * 一行内cell集合
     */
    private List<String> cellList = new ArrayList<String>();

    /**
     * 前一行的cell集合，用于处理合并单元格
     */
    private List<String> preCellList = new ArrayList<>();
    /**
     * 判断整行是否为空行的标记
     */
    private boolean flag = false;
    /**
     * 当前行
     */
    private int curRow = 1;
    /**
     * 当前列
     */
    private int curCol = 0;
    /**
     * T元素标识
     */
    private boolean isTElement;
    /**
     * 异常信息，如果为空则表示没有异常
     */
    private String exceptionMessage;

    /**
     * 单元格数据类型，默认为字符串类型
     */
    private CellDataType nextDataType = CellDataType.SSTINDEX;

    private final DataFormatter formatter = new DataFormatter();
    /**
     * 单元格存储格式的索引，对应style.xml中的numFmts元素的子元素索引
     */
    private short numFmtIndex;
    /**
     * 单元格存储的格式化字符串，nmtFmt的formateCode属性的值
     */
    private String numFmtString;
    /**
     * 当前列坐标，如A1
     */
    private String curCoordinate = null;
    /**
     * 定义前一列（元素），用来计算其中空的单元格数量
     */
    private String preCoordinate = null;
    /**
     * 定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
     */
    private String maxCellCoordinate = null;
    /**
     * 单元格的格式表，对应style.xml
     */
    private StylesTable stylesTable;
    /**
     * 总行数
     */
    private Integer totalRowCount = 0;
    /**
     * 记录合并单元格的范围值
     */
    private List<String> mergeCelRanges = new ArrayList<>();
    /**
     * 遍历工作簿中所有的电子表格
     * 并缓存在mySheetList中
     *
     * @param filename
     * @throws Exception
     */
    public int process(String filename) throws Exception {
//        filePath = filename;
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader xssfReader = new XSSFReader(pkg);
        stylesTable = xssfReader.getStylesTable();
        SharedStringsTable sst = xssfReader.getSharedStringsTable();
        // 1
//        XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
        // 2
        XMLReader parser = XMLReaderFactory.createXMLReader();
        // 3 效果和2默认的类型一样
//        SAXParserFactory m_parserFactory = null;
//        // If unable to create an instance, let's try to use the XMLReader from JAXP
//        m_parserFactory = SAXParserFactory.newInstance();
//        m_parserFactory.setNamespaceAware(true);
//        XMLReader parser = m_parserFactory.newSAXParser().getXMLReader();
        // 4 官方
//        XMLReader parser = SAXHelper.newXMLReader();
        this.sharedStringsTable = sst;
        parser.setContentHandler(this);
        XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        //遍历sheet
        while (sheets.hasNext()) {
            //标记初始行为第一行
            curRow = 1;
            sheetIndex++;
            //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错
            InputStream sheet = sheets.next();
            sheetName = sheets.getSheetName();
            // 解析Excel时下拉选项会被解析成隐藏的sheet，这边过滤不处理
            if (sheetName.contains(ExcelConstant.HIDDEN_SHEET)) {
                continue;
            }
            InputSource sheetSource = new InputSource(sheet);
            //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
            parser.parse(sheetSource);
            closeStream(sheet);
        }
        //返回该excel文件的总行数，不包括首列和空行
        return totalRows;
    }


    /**
     * 关闭流
     *
     * @param inputStream
     */
    public void closeStream(InputStream inputStream) {
        if (inputStream != null) {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 第一个执行
     *
     * @param uri
     * @param localName
     * @param name
     * @param attributes
     * @throws SAXException
     */
    @Override
    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
        // 获取总行号  格式： A1:B5    取最后一个值即可
//        if("dimension".equals(name)) {
//            String dimensionStr = attributes.getValue("ref");
//            totalRowCount = Integer.parseInt(dimensionStr.substring(dimensionStr.indexOf(":") + 2)) - 1;
//        }

//        if ("mergeCell".equals(name)) {
//            String mergeCellStr = attributes.getValue("ref");
//
//        }

        //c => 单元格
        if (ExcelConstant.C_ELEMENT.equals(name)) {
            //前一个单元格的位置
            if (preCoordinate == null) {
                preCoordinate = attributes.getValue(ExcelConstant.R_ATTR);
            } else {
                preCoordinate = curCoordinate;
            }

            //当前单元格的位置
            curCoordinate = attributes.getValue(ExcelConstant.R_ATTR);
            //设定单元格类型
            this.setNextDataType(attributes);
        } else if (ExcelConstant.MERGE_CELL_ELEMENT.equals(name)) {
            mergeCelRanges.add(attributes.getValue(ExcelConstant.MERGE_CELL_REF_ATTR_VALUE));
        }

        //当元素为t时
        if (ExcelConstant.T_ELEMENT.equals(name)) {
            isTElement = true;
        } else {
            isTElement = false;
        }

        //置空
        lastContent = "";
    }

    /**
     * 第二个执行
     * 得到单元格对应的索引值或是内容值
     * 如果单元格类型是字符串、INLINESTR、数字、日期，lastIndex则是索引值
     * 如果单元格类型是布尔值、错误、公式，lastIndex则是内容值
     * @param ch
     * @param start
     * @param length
     * @throws SAXException
     */
    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        lastContent += new String(ch, start, length);
    }

    /**
     * 第三个执行
     *
     * @param uri
     * @param localName
     * @param name
     * @throws SAXException
     */
    @Override
    public void endElement(String uri, String localName, String name) throws SAXException {

        //t元素也包含字符串
        if (isTElement) {
            //将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
            String value = lastContent.trim();
            cellList.add(curCol, value);
            curCol++;
            isTElement = false;
            //如果里面某个单元格含有值，则标识该行不为空行
            if (value != null && !"".equals(value)) {
                flag = true;
            }
        } else if (ExcelConstant.V_ELEMENT.equals(name)) {
            //v => 单元格的值，如果单元格是字符串，则v标签的值为该字符串在SST中的索引
            //根据索引值获取对应的单元格值
            String value = this.getDataValue(lastContent.trim(), "");
            //补全单元格之间的空单元格
            fillBlackCell(curCoordinate, preCoordinate, false);
            cellList.add(curCol, value);
            curCol++;
            //如果里面某个单元格含有值，则标识该行不为空行
            if (value != null && !"".equals(value)) {
                flag = true;
            }
        } else {
            //如果标签名称为row，这说明已到行尾，调用optRows()方法
            if (ExcelConstant.ROW_ELEMENT.equals(name)) {
                //默认第一行为表头，以该行单元格数目为最大数目
                if (curRow == 1) {
                    maxCellCoordinate = curCoordinate;
                }
                //补全一行尾部可能缺失的单元格
                if (maxCellCoordinate != null) {
                    fillBlackCell(maxCellCoordinate, curCoordinate, true);
                }

                if (flag && curRow != 1){
                    //该行不为空行且该行不是第一行，则发送（第一行为列名，不需要）
//                    ExcelReaderUtil.sendRows(filePath, sheetName, sheetIndex, curRow, cellList);
                    totalRowCount++;
                    totalRows++;
                    preCellList.clear();
                    preCellList.addAll(cellList);
                    excelReadDataDelegated.readExcelData(sheetName, sheetIndex, totalRowCount, curRow, cellList);
                }

                cellList.clear();
                curRow++;
                curCol = 0;
                preCoordinate = null;
                curCoordinate = null;
                flag = false;
            }
        }
    }

    /**
     * Description: 填充空白单元格
     * @param curCoordinate
     * @param preCoordinate
     * @param isEnd 是否是末尾
     * @return void
     * @throws
     * @author 魏荣杰
     * @date 2019/11/13 7:55 下午
     * @since
     */
    private void fillBlackCell(String curCoordinate, String preCoordinate, boolean isEnd) {
        if (!curCoordinate.equals(preCoordinate)) {
            int len = countNullCell(curCoordinate, preCoordinate, isEnd);
            for (int i = 0; i < len; i++) {
                cellList.add(curCol, "");
                curCol++;
            }
        }
    }

    /**
     * Description:计算当前单元格和前一个单元格之间的空白单元格数量,如果是尾部则不减1
     * @param ref
     * @param preRef
     * @param isEnd
     * @return int
     * @author 魏荣杰
     * @date 2019/11/13 6:46 下午
     * @since v4.6.0
     */
    public int countNullCell(String ref, String preRef, boolean isEnd) {
        //excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
        String xfd = ref.replaceAll("\\d+", "");
        String preXfd = preRef.replaceAll("\\d+", "");

        xfd = fillChar(xfd, ExcelConstant.MAX_CELL_BIT, ExcelConstant.CELL_FILL_STR, true);
        preXfd = fillChar(preXfd, ExcelConstant.MAX_CELL_BIT, ExcelConstant.CELL_FILL_STR, true);

        char[] letter = xfd.toCharArray();
        char[] preLetter = preXfd.toCharArray();
        int res = (letter[0] - preLetter[0]) * 26 * 26 + (letter[1] - preLetter[1]) * 26 + (letter[2] - preLetter[2]);
        if (!isEnd) {
            res = res - 1;
        }
        return res;
    }


    /**
     * Description: 判断某个单元格是否在单元格组内
     * @param ref
     * @param mergeCellRanges
     * @return boolean
     * @author 魏荣杰
     * @date 2019/11/15 8:22 下午
     * @since v4.6.0
     */
    public boolean checkIsMergeCell(String ref, List<String> mergeCellRanges) {
        String refXfd = ref.replaceAll(ExcelConstant.RANGE_CHECK_COORDINATE_REGEX, "");
        refXfd = fillChar(refXfd, ExcelConstant.MAX_CELL_BIT, ExcelConstant.CELL_FILL_STR, true);

        Pattern p =Pattern.compile(ExcelConstant.RANGE_CHECK_REGEX);
        Matcher m = p.matcher(ref);
        String refNum = m.replaceAll("").trim();

        char[] refLetter = refXfd.toCharArray();
        String[] ranges;
        for (String mergeCellRange : mergeCellRanges) {
            ranges = mergeCellRange.split(ExcelConstant.COORDINATE_SPLIT);
            String first = ranges[0];
            String second = ranges[1];
            String firstStr = first.replaceAll(ExcelConstant.RANGE_CHECK_COORDINATE_REGEX, "");
            String secondStr = second.replaceAll(ExcelConstant.RANGE_CHECK_COORDINATE_REGEX, "");
            firstStr = fillChar(firstStr, ExcelConstant.MAX_CELL_BIT, ExcelConstant.CELL_FILL_STR, true);
            secondStr = fillChar(secondStr, ExcelConstant.MAX_CELL_BIT, ExcelConstant.CELL_FILL_STR, true);
            m = p.matcher(first);
            String firstNum = m.replaceAll("").trim();
            m = p.matcher(second);
            String secondNum = m.replaceAll("").trim();

            char[] firstChar = firstStr.toCharArray();
            char[] secondChar = secondStr.toCharArray();

            int rangeCharNum = (secondChar[0] - firstChar[0]) * 26 * 26 + (secondChar[1] - firstChar[1]) * 26 + (secondChar[2] - firstChar[2]);
            int rangeNum = Integer.parseInt(secondNum) - Integer.parseInt(firstNum);

            int refRangeCharNum = (refLetter[0] - firstChar[0]) * 26 * 26 + (refLetter[1] - firstChar[1]) * 26 + (refLetter[2] - firstChar[2]);
            int refCharNum = Integer.parseInt(refNum) - Integer.parseInt(firstNum);
            if (refRangeCharNum <= rangeCharNum && refCharNum <= rangeNum) {
                return true;
            }
        }

        return false;
    }

    /**
     * 处理数据类型
     *
     * @param attributes
     */
    public void setNextDataType(Attributes attributes) {
        //cellType为空，则表示该单元格类型为数字
        nextDataType = CellDataType.NUMBER;
        numFmtIndex = -1;
        numFmtString = null;
        //单元格类型
        String cellType = attributes.getValue(ExcelConstant.T_ATTR_VALUE);
        String cellStyleStr = attributes.getValue(ExcelConstant.S_ATTR_VALUE);
        //获取单元格的位置，如A1,B1
        String ref = attributes.getValue(ExcelConstant.R_ATTR);

        if (CellDataType.BOOL.getName().equals(cellType)) {
            //处理布尔值
            nextDataType = CellDataType.BOOL;
        } else if (CellDataType.ERROR.getName().equals(cellType)) {
            //处理错误
            nextDataType = CellDataType.ERROR;
        } else if (CellDataType.INLINESTR.getName().equals(cellType)) {
            nextDataType = CellDataType.INLINESTR;
        } else if (CellDataType.SSTINDEX.getName().equals(cellType)) {
            //处理字符串
            nextDataType = CellDataType.SSTINDEX;
        } else if (CellDataType.FORMULA.getName().equals(cellType)) {
            nextDataType = CellDataType.FORMULA;
        }

        if (cellStyleStr != null) {
            //处理日期
            int styleIndex = Integer.parseInt(cellStyleStr);
            XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
            numFmtIndex = style.getDataFormat();
            numFmtString = style.getDataFormatString();

            if (numFmtString.contains(CellDataType.DATE.getName())) {
                nextDataType = CellDataType.DATE;
                numFmtString = ExcelConstant.DATE_FORMAT;
            }

            if (numFmtString == null) {
                nextDataType = CellDataType.NULL;
                numFmtString = BuiltinFormats.getBuiltinFormat(numFmtIndex);
            }
        }

        if (checkIsMergeCell(ref, mergeCelRanges)) {
            cellList.add(curCol++, "");
        }
    }

    /**
     * 对解析出来的数据进行类型处理
     * @param value   单元格的值，
     *                value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值，
     *                SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值
     * @param thisStr 一个空字符串
     * @author 魏荣杰
     * @date 2019/11/14 11:18 上午
     */
    @SuppressWarnings("deprecation")
    public String getDataValue(String value, String thisStr) {
        switch (nextDataType) {
            // 这几个的顺序不能随便交换，交换了很可能会导致数据错误
            case BOOL:
                //布尔值
                char first = value.charAt(0);
                thisStr = first == '0' ? "FALSE" : "TRUE";
                break;
            case ERROR:
                //错误
                thisStr = "\"ERROR:" + value.toString() + '"';
                break;
            case FORMULA:
                //公式
                thisStr = '"' + value.toString() + '"';
                break;
            case INLINESTR:
                XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                thisStr = rtsi.toString();
                //释放
                rtsi = null;
                break;
            case SSTINDEX:
                //字符串
                String sstIndex = value.toString();
                try {
                    int idx = Integer.parseInt(sstIndex);
                    //根据idx索引值获取内容值
                    XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
                    thisStr = rtss.toString();
                    // 释放
                    rtss = null;
                } catch (NumberFormatException ex) {
                    thisStr = value.toString();
                }
                break;
            case NUMBER:
                //数字
                if (numFmtString != null) {
                    thisStr = formatter.formatRawCellContents(Double.parseDouble(value), numFmtIndex, numFmtString).trim();
                } else {
                    thisStr = value;
                }
                thisStr = thisStr.replace("_", "").trim();
                break;
            case DATE:
                //日期
                thisStr = formatter.formatRawCellContents(Double.parseDouble(value), numFmtIndex, numFmtString);
                // 对日期字符串作特殊处理，去掉T
                thisStr = thisStr.replace("T", " ");
                break;
            default:
                thisStr = " ";
                break;
        }
        return thisStr;
    }


    /**
     * Description:将不足指定位数的字符串补全，高位补上指定字符串
     * @param str
     * @param maxLen
     * @param let
     * @param isPre
     * @return java.lang.String
     * @throws
     * @author 魏荣杰
     * @date 2019/11/13 6:46 下午
     * @since
     */
    private String fillChar(String str, int maxLen, String let, boolean isPre) {
        int len_1 = str.length();
        if (len_1 < maxLen) {
            if (isPre) {
                for (int i = 0; i < (maxLen - len_1); i++) {
                    str = let + str;
                }
            } else {
                for (int i = 0; i < (maxLen - len_1); i++) {
                    str = str + let;
                }
            }
        }
        return str;
    }

    /**
     * @return the exceptionMessage
     */
    public String getExceptionMessage() {
        return exceptionMessage;
    }
}